﻿/********************************************************************************
    Copyright (C) Binod Nepal, Planet Earth Solutions Pvt. Ltd., Kathmandu.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace MixNP.DatabaseLayer
{
    public static class Items
    {
        public static bool Hit(double itemId)
        {
            string sql = "UPDATE dbo.Items SET TotalViews = TotalViews + 1 WHERE ItemId=@ItemId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static double GetRandomItemId(double itemId)
        {
            string sql = "SELECT TOP 1 * FROM items WHERE ItemId!=@ItemId AND dbo.GetRandomImagePath(ItemId) IS NOT NULL ORDER BY NEWID();";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.Utility.Conversion.TryCastDouble(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }        
        }

        public static bool SaveItemImage(double itemId, string filePath)
        {
            string sql = "INSERT INTO dbo.ItemImages(ItemId, FilePath) SELECT @ItemId, @FilePath;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                command.Parameters.AddWithValue("@FilePath", filePath);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }
        
        public static bool DeleteItemImage(double imageId)
        {
            string sql = "UPDATE dbo.ItemImages SET IsDeleted=1 WHERE ImageId=@ImageId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ImageId", imageId);
                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }
        
        public static bool DeleteItemImage(string fileName)
        {
            string sql = "UPDATE dbo.ItemImages SET IsDeleted=1 WHERE FilePath=@FilePath;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@FilePath", fileName);
                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static double SaveItem(int categoryId, string emailAddress, string name, string title, decimal price, short priceType, string details, List<KeyValuePair<string, string>> customFields, List<KeyValuePair<string, string>> otherFields)
        {
            string sql = "dbo.InsertItem";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@CategoryId", categoryId);
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@AddedBy", name);
                command.Parameters.AddWithValue("@Title", title);

                if (price.Equals(0) || price.Equals(decimal.MinValue))
                {
                    command.Parameters.AddWithValue("@Price", 0);
                }
                else
                {
                    command.Parameters.AddWithValue("@Price", price);                
                }

                command.Parameters.AddWithValue("@PriceType", priceType);
                command.Parameters.AddWithValue("@Details", details);
                command.Parameters.AddWithValue("@CustomFieldsTable", Pes.Utility.Conversion.ConvertListToDataTable(customFields));
                command.Parameters.AddWithValue("@OtherFieldsTable", Pes.Utility.Conversion.ConvertListToDataTable(otherFields));

                command.Parameters.Add("@ItemId", SqlDbType.BigInt);
                command.Parameters["@ItemId"].Direction = ParameterDirection.Output;

                if (Pes.DBFactory.DBOperations.ExecuteNonQuery(command))
                {
                    return Pes.Utility.Conversion.TryCastDouble(command.Parameters["@ItemId"].Value);
                }

                return 0;
            }
        }

        public static bool UpdateItem(double itemId, int categoryId, string title, decimal price, short priceType, string details, List<KeyValuePair<string, string>> customFields, List<KeyValuePair<string, string>> otherFields)
        {
            string sql = "dbo.UpdateItem";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@ItemId", itemId);
                command.Parameters.AddWithValue("@CategoryId", categoryId);
                command.Parameters.AddWithValue("@Title", title);
                command.Parameters.AddWithValue("@Price", price);
                command.Parameters.AddWithValue("@PriceType", priceType);
                command.Parameters.AddWithValue("@Details", details);
                command.Parameters.AddWithValue("@CustomFieldsTable", Pes.Utility.Conversion.ConvertListToDataTable(customFields));
                command.Parameters.AddWithValue("@OtherFieldsTable", Pes.Utility.Conversion.ConvertListToDataTable(otherFields));

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static string GetTitle(double itemId)
        {
            string sql = "SELECT Title FROM dbo.Items WHERE ItemId=@ItemId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static string GetFirstImage(double itemId)
        {
            string sql = "SELECT FilePath FROM dbo.ItemImages WHERE ImageId = (SELECT MIN(ImageId) FROM dbo.ItemImages WHERE ItemId=@ItemId);";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static string GetRandomImage(double itemId)
        {
            string sql = "SELECT FilePath FROM dbo.ItemImages WHERE ItemId=@ItemId ORDER BY NEWID();";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static string GetAuthorEmail(double itemId)
        {
            string sql = "SELECT EmailAddress FROM dbo.Items WHERE ItemId=@ItemId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static bool IsParentCategory(double itemId, int categoryId)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.Items WHERE ItemId=@ItemId AND CategoryId=@CategoryId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                command.Parameters.AddWithValue("@CategoryId", categoryId);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)).Equals("1"));
            }
        }

        public static DataTable GetItem(double itemId)
        {
            string sql = "SELECT * FROM dbo.ItemView WHERE ItemId=@ItemId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }

        public static DataTable GetLatestItems()
        {
            string sql = "SELECT TOP 20 *, dbo.GetRandomImagePath(ItemId) AS FilePath FROM Items WHERE dbo.GetRandomImagePath(ItemId) IS NOT NULL AND Price > 0 ORDER BY AddedOn DESC;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }

        public static DataTable GetPopularItems()
        {
            string sql = "SELECT TOP 20 *, dbo.GetRandomImagePath(ItemId) AS FilePath FROM Items WHERE /*WasRenewed=0 AND */ dbo.GetRandomImagePath(ItemId) IS NOT NULL AND Price > 0 ORDER BY TotalViews DESC;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }
        

        public static DataTable GetItemAliasView(double itemId)
        {
            string sql = "SELECT * FROM dbo.ItemAliasView WHERE ItemId=@ItemId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }

        public static DataTable GetCustomFields(double itemId)
        {
            string sql = "SELECT * FROM dbo.ItemCustomFieldView WHERE ItemId=@ItemId AND Status=1;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }


        public static DataTable GetOtherFields(double itemId)
        {
            string sql = "SELECT * FROM dbo.ItemOtherFields WHERE ItemId=@ItemId AND Status=1;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }


        public static DataTable GetImages(double itemId)
        {
            string sql = "SELECT * FROM dbo.ItemImages WHERE ItemId=@ItemId AND IsDeleted=0;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }

        public static string GetParentCategoryAlias(double itemId)
        {
            string sql = "SELECT dbo.GetParentCategoryAliasByItemId(@ItemId);";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static string GetCategoryAlias(double itemId)
        {
            string sql = "SELECT dbo.GetCategoryAliasByItemId(@ItemId);";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static bool AddToWatchlist(string emailAddress, double itemId)
        {
            string sql = @"INSERT INTO dbo.Watchlist(EmailAddress, ItemId)
                            SELECT @EmailAddress, @ItemId
                            WHERE NOT EXISTS
                            (
	                            SELECT TOP 1 1 FROM dbo.Watchlist
	                            WHERE EmailAddress=@EmailAddress
	                            AND ItemId = @ItemId
                            );";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static bool RemoveFromWatchlist(string emailAddress, double itemId)
        {
            string sql = @"DELETE FROM dbo.Watchlist
	                        WHERE EmailAddress=@EmailAddress
	                        AND ItemId = @ItemId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static bool AddToWishlist(string emailAddress, double itemId)
        {
            string sql = @"INSERT INTO dbo.Wishlist(EmailAddress, ItemId)
                            SELECT @EmailAddress, @ItemId
                            WHERE NOT EXISTS
                            (
	                            SELECT TOP 1 1 FROM dbo.Wishlist
	                            WHERE EmailAddress=@EmailAddress
	                            AND ItemId = @ItemId
                            );";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static bool RemoveFromWishlist(string emailAddress, double itemId)
        {
            string sql = @"DELETE FROM dbo.Wishlist
	                        WHERE EmailAddress=@EmailAddress
	                        AND ItemId = @ItemId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@ItemId", itemId);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static bool Renew(double itemId)
        {
            string sql = "UPDATE dbo.Items SET ItemStatusId=0, ExpiresOn=GETDATE() + 60, WasRenewed=1 WHERE ItemId=@ItemId AND ItemStatusId<>1;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static bool Withdraw(double itemId)
        {
            string sql = "UPDATE dbo.Items SET ItemStatusId=2 WHERE ItemId=@ItemId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static bool FlagSold(double itemId)
        {
            string sql = "UPDATE dbo.Items SET ItemStatusId=1 WHERE ItemId=@ItemId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

    }
}
